var express = require('express');
var Msdb = require("../../../db/Mysqldb.js");
var router = express.Router();   // 可使用 express.Router 类创建模块化、可挂载的路由句柄

var db = new Msdb();
var getTime = () => {
    return new Date(new Date().getTime() + 8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' '); // 获取系统时间并转为数据库接受的格式
};

router.post("/getBom", async function (req, res) {
    var result = {};
    try {
        // 1. 从请求体获取参数并设置默认值
        var data = req.body;
        var number = data.number || '';
        var productName = data.productName || '';
        var specificationAndModel = data.specificationAndModel || '';
        var parentNumber = data.parentNumber || '';
        var topLevel = data.topLevel || false; // 新增参数，是否只查询顶级BOM
        var pageNum = parseInt(data.pageNum || 1, 10);
        var pageSize = parseInt(data.pageSize || 10, 10);
        var offset = (pageNum - 1) * pageSize;

        // 2. 构建查询条件
        var queryParams = [];
        var whereClause = [];

        if (number) {
            whereClause.push('number LIKE ?');
            queryParams.push(`%${number}%`);
        }
        if (productName) {
            whereClause.push('product_name LIKE ?');
            queryParams.push(`%${productName}%`);
        }
        if (specificationAndModel) {
            whereClause.push('specification_and_model LIKE ?');
            queryParams.push(`%${specificationAndModel}%`);
        }

        // 处理parent_number条件
        if (topLevel) {
            // 只查询顶级BOM
            whereClause.push(`parent_number = ''`);
        } else if (parentNumber !== '') {
            // 非顶级BOM查询，但包括顶级BOM和指定父编号的BOM
            whereClause.push('(parent_number IS NULL OR parent_number = ?)');
            queryParams.push(parentNumber);
        }

        var whereSql = whereClause.length ? `WHERE ${whereClause.join(' AND ')}` : '';

        // 3. 查询总条数
        var countResult = await db.query(
            `SELECT COUNT(*) AS total FROM bom ${whereSql}`,
            queryParams
        );
        var total = countResult[0].total;

        // 4. 查询当前页数据
        var queryParamsWithPage = [...queryParams, pageSize, offset];
        var bomList = await db.query(
            `SELECT id, number, parent_number, product_name, specification_and_model 
             FROM bom ${whereSql} 
             ORDER BY id DESC
             LIMIT ? OFFSET ?`,
            queryParamsWithPage
        );

        // 5. 组装结果
        var resultList = bomList.map(item => ({
            id: item.id,
            number: item.number,
            parentNumber: item.parent_number,
            productName: item.product_name,
            specificationAndModel: item.specification_and_model
        }));

        // 6. 构建返回结果
        result = {
            code: 200,
            msg: "查询成功",
            data: {
                list: resultList,
                pagination: {
                    pageNum: pageNum,
                    pageSize: pageSize,
                    total: total,
                    totalPages: Math.ceil(total / pageSize)
                }
            }
        };
    } catch (error) {
        console.error('获取BOM失败:', error);
        result = {
            code: 500,
            msg: '服务器错误，获取BOM失败',
            error: error.message
        };
    }
    res.send(JSON.stringify(result));
});
    
router.post("/addBom", async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    var result = {};
    
    try {
        var data = req.body;
        
        // 1. 验证必填字段
        if (!data.number) {
            result = { code: 400, msg: "产品编号不能为空" };
            return res.send(JSON.stringify(result));
        }
        if (!data.productName) {
            result = { code: 400, msg: "产品品名不能为空" };
            return res.send(JSON.stringify(result));
        }

        // 开启事务
        await db.beginTransaction(connection);

        // 2. 检查产品编号是否已存在
        const [existBomResult] = await connection.execute(
            `SELECT id FROM bom WHERE number = ?`,
            [data.number]
        );
        if (existBomResult.length > 0) {
            result = { code: 400, msg: `产品编号${data.number}已存在` };
            return res.send(JSON.stringify(result));
        }

        // 3. 层级校验（当前限制为最多两层）
        if (data.parentNumber) {
            // 3.1 检查父级编号是否存在
            const [parentBomResult] = await connection.execute(
                `SELECT parent_number FROM bom WHERE number = ?`,
                [data.parentNumber]
            );
            
            if (parentBomResult.length === 0) {
                result = { code: 400, msg: `父级编号${data.parentNumber}不存在` };
                return res.send(JSON.stringify(result));
            }
            
            // 3.2 检查父级是否还有父级（如果有则超出两层限制）
            if (parentBomResult[0].parent_number) {
                result = { code: 400, msg: "当前最多支持两层BOM，无法添加" };
                return res.send(JSON.stringify(result));
            }
        }

        // 4. 插入新BOM记录
        const [insertResult] = await connection.execute(
            `INSERT INTO bom 
             (number, parent_number, product_name, specification_and_model) 
             VALUES (?, ?, ?, ?)`,
            [
                data.number,
                data.parentNumber || '',  // 父级编号可为空
                data.productName,
                data.specificationAndModel || ''  // 规格可为空
            ]
        );

        // 5. 记录操作日志
        var sqlWords = `INSERT INTO bom (number, parent_number, product_name, specification_and_model) 
                       VALUES ('${data.number}', '${data.parentNumber || ''}', '${data.productName}', '${data.specificationAndModel || ''}')`;
                       
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
            [req.auth.user_id, sqlWords, getTime()]
        );

        // 提交事务
        await db.commit(connection);

        // 6. 返回成功结果
        result = {
            code: 200,
            msg: "BOM新增成功",
            data: { id: insertResult.insertId }  // 返回新增记录的ID
        };
    } catch (error) {
        // 回滚事务
        await db.rollback(connection);
        console.error('新增BOM失败:', error);
        result = {
            code: 500,
            msg: '服务器错误，新增BOM失败',
            error: error.message
        };
    } finally {
        // 释放连接
        connection.release();
    }
    
    res.send(JSON.stringify(result));
});

router.post("/deleteBom", async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    var result = {};
    
    try {
        var data = req.body;
        
        // 1. 验证必填字段
        if (!data.number) {
            result = { code: 400, msg: "BOM编号不能为空" };
            return res.send(JSON.stringify(result));
        }

        // 开启事务
        await db.beginTransaction(connection);

        // 2. 检查BOM编号是否存在
        const [existBomResult] = await connection.execute(
            `SELECT id FROM bom WHERE number = ?`,
            [data.number]
        );
        if (existBomResult.length === 0) {
            result = { code: 400, msg: `BOM编号${data.number}不存在` };
            return res.send(JSON.stringify(result));
        }

        // 3. 检查该BOM编号是否被产品表的product_part_number引用
        const [productRefResult] = await connection.execute(
            `SELECT id FROM product WHERE product_part_number = ?`,
            [data.number]
        );
        if (productRefResult.length > 0) {
            result = { 
                code: 400, 
                msg: `BOM编号${data.number}已被产品引用，无法删除` 
            };
            return res.send(JSON.stringify(result));
        }

        // 4. 检查是否有子BOM引用当前BOM
        const [childBomResult] = await connection.execute(
            `SELECT id FROM bom WHERE parent_number = ?`,
            [data.number]
        );
        if (childBomResult.length > 0) {
            result = { 
                code: 400, 
                msg: `BOM编号${data.number}存在子BOM，无法删除` 
            };
            return res.send(JSON.stringify(result));
        }

        // 5. 执行删除操作
        const [deleteResult] = await connection.execute(
            `DELETE FROM bom WHERE number = ?`,
            [data.number]
        );

        // 6. 记录操作日志
        var sqlWords = `DELETE FROM bom WHERE number = '${data.number}'`;
        
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
            [req.auth.user_id, sqlWords, getTime()]
        );

        // 提交事务
        await db.commit(connection);

        // 7. 返回成功结果
        result = {
            code: 200,
            msg: `BOM编号${data.number}删除成功`,
            data: { affectedRows: deleteResult.affectedRows }
        };
    } catch (error) {
        // 回滚事务
        await db.rollback(connection);
        console.error('删除BOM失败:', error);
        result = {
            code: 500,
            msg: '服务器错误，删除BOM失败',
            error: error.message
        };
    } finally {
        // 释放连接
        connection.release();
    }
    
    res.send(JSON.stringify(result));
});

module.exports = router;